package org.compiere.model;


import java.awt.Container;
import java.io.File;
import java.math.BigDecimal;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;

import org.compiere.apps.IProcessParameter;
import org.compiere.apps.ProcessCtl;
import org.compiere.apps.ProcessParameterPanel;
import org.compiere.apps.Waiting;
import org.compiere.process.DocAction;
import org.compiere.process.DocumentEngine;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.util.ASyncProcess;
import org.compiere.util.CCache;
import org.compiere.util.CLogger;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;

import javax.swing.JFrame;

public class Mhrmpayment extends X_hrm_payment implements DocAction, ASyncProcess{
	
	/**
	 * 
	 */
	private static final long serialVersionUID = -5616959835908036106L;
	
	/**/
	private int AD_Process_ID;
	/**/
	ASyncProcess m_parent;

	public Mhrmpayment(Properties ctx, int hrm_payment_ID, String trxName) {
		super(ctx, hrm_payment_ID, trxName);

		if (hrm_payment_ID == 0)
		{
			setDocStatus (DOCSTATUS_Drafted);		//	Draft
			setDocAction (DOCACTION_Complete);
			setIsSOTrx (true);
			setIsApproved (false);
			setIsTransferred (false);
			setPosted(false);
			super.setProcessed (false);
			setProcessing(false);
		}

	
	}

	public Mhrmpayment(Properties ctx, ResultSet rs, String trxName) {
		super(ctx, rs, trxName);
	}

		/**
		 * 	Get HRPayment from Cache
		 *	@param ctx context
		 *	@param hrm_payment_ID id
		 *	@return Mhrmpayment
		 */
		public static Mhrmpayment get (Properties ctx, int hrm_payment_ID)
		{
			Integer key = new Integer (hrm_payment_ID);
			Mhrmpayment retValue = (Mhrmpayment) s_cache.get (key);
			if (retValue != null)
				return retValue;
			retValue = new Mhrmpayment (ctx, hrm_payment_ID, null);
			if (retValue.get_ID () != 0)
				s_cache.put (key, retValue);
			return retValue;
		} //	get

		/**	Cache						*/
		private static CCache<Integer,Mhrmpayment>	s_cache	= new CCache<Integer,Mhrmpayment>("hrm_payment", 20, 2);	//	2 minutes

		/**	Logger			*/
		private static CLogger s_log = CLogger.getCLogger(Mhrmpayment.class);

		/**
		 * 	Overwrite Client/Org if required
		 * 	@param AD_Client_ID client
		 * 	@param AD_Org_ID org
		 */
		public void setClientOrg (int AD_Client_ID, int AD_Org_ID)
		{
			super.setClientOrg(AD_Client_ID, AD_Org_ID);
		}	//	setClientOrg

		/**
		 * 	Set Business Partner Defaults & Details
		 * 	@param bp business partner
		 */

		/** Reversal Flag		*/
		private boolean m_reversal = false;

		/**
		 * 	Is Reversal
		 *	@return reversal
		 */
		public boolean isReversal()
		{
			return m_reversal;
		}	//	isReversal

		/**
		 * 	Set Reversal
		 *	@param reversal reversal
		 */
		private void setReversal(boolean reversal)
		{
			m_reversal = reversal;
		}	//	setReversal
		/**
		 * 	Set Processed.
		 * 	Propergate to Lines/Taxes
		 *	@param processed processed
		 */
		public void setProcessed (boolean processed)
		{
			super.setProcessed (processed);
			if (get_ID() == 0)
				return;
			String set = "SET Processed='"
				+ (processed ? "Y" : "N")
				+ "' WHERE hrm_payment_ID = " + gethrm_payment_ID();
			DB.executeUpdate("UPDATE hrm_payment " + set, get_TrxName());
		}	//	setProcessed

		/**
		 * 	Validate HRPayment Pay Schedule
		 *	@return pay schedule is valid
		 */
		public boolean validatePaySchedule()
		{
			return true;//valid;
		}	//	validatePaySchedule


		/**************************************************************************
		 * 	Before Save
		 *	@param newRecord new
		 *	@return true
		 */
		protected boolean beforeSave (boolean newRecord)
		{
			return true;
		}	//	beforeSave

		/**
		 * 	Before Delete
		 *	@return true if it can be deleted
		 */
		protected boolean beforeDelete ()
		{
			return true;
		}	//	beforeDelete

		/**
		 * 	String Representation
		 *	@return info
		 */
		public String toString ()
		{
			return "" + get_ID();
		}	//	toString


		/**
		 * 	After Save
		 *	@param newRecord new
		 *	@param success success
		 *	@return success
		 */
		protected boolean afterSave (boolean newRecord, boolean success)
		{
			return true;
		}	//	afterSave

		/**
		 * 	Get Document Status
		 *	@return Document Status Clear Text
		 */
		public String getDocStatusName()
		{
			return MRefList.getListName(getCtx(), 131, getDocStatus());
		}	//	getDocStatusName


		/**************************************************************************
		 * 	Process document
		 *	@param processAction document action
		 *	@return true if performed
		 */
		public boolean processIt (String processAction)
		{
			m_processMsg = null;
			DocumentEngine engine = new DocumentEngine (this, getDocStatus());
			return engine.processIt (processAction, getDocAction());
		}	//	process

		/**	Process Message 			*/
		private String		m_processMsg = null;
		/**	Just Prepared Flag			*/
		private boolean		m_justPrepared = false;

		/**
		 * 	Unlock Document.
		 * 	@return true if success
		 */
		public boolean unlockIt()
		{
			log.info("unlockIt - " + toString());
			setProcessing(false);
			return true;
		}	//	unlockIt

		/**
		 * 	Invalidate Document
		 * 	@return true if success
		 */
		public boolean invalidateIt()
		{
			log.info("invalidateIt - " + toString());
			setDocAction(DOCACTION_Prepare);
			return true;
		}	//	invalidateIt

		/**
		 *	Prepare Document
		 * 	@return new status (In Progress or Invalid)
		 */
		public String prepareIt()
		{
			log.info(toString());
			m_processMsg = ModelValidationEngine.get().fireDocValidate(this, ModelValidator.TIMING_BEFORE_PREPARE);
			if (m_processMsg != null)
				return DocAction.STATUS_Invalid;

			//	Convert/Check DocType
			if (getC_DocType_ID() == 0)
			{
				m_processMsg = "No Document Type";
				return DocAction.STATUS_Invalid;
			}


	
			m_processMsg = ModelValidationEngine.get().fireDocValidate(this, ModelValidator.TIMING_AFTER_PREPARE);
			if (m_processMsg != null)
				return DocAction.STATUS_Invalid;
	
			//	Add up Amounts
			m_justPrepared = true;
			if (!DOCACTION_Complete.equals(getDocAction()))
				setDocAction(DOCACTION_Complete);
			return DocAction.STATUS_InProgress;
		}	//	prepareIt

		/**
		 * 	Approve Document
		 * 	@return true if success
		 */
		public boolean  approveIt()
		{
			log.info(toString());
			setIsApproved(true);
			return true;
		}	//	approveIt

		/**
		 * 	Reject Approval
		 * 	@return true if success
		 */
		public boolean rejectIt()
		{
			log.info(toString());
			setIsApproved(false);
			return true;
		}	//	rejectIt

		/**
		 * 	Complete Document
		 * 	@return new status (Complete, In Progress, Invalid, Waiting ..)
		 */
		public String completeIt()
		{
			//getAD_Process_ID();
			
			if(AD_Process_ID != 0){
				//  Prepare Process
				ProcessInfo pi = new ProcessInfo ("The printing process of ", AD_Process_ID);
				pi.setAD_User_ID (Env.getAD_User_ID(Env.getCtx()));
				pi.setAD_Client_ID(Env.getAD_Client_ID(Env.getCtx()));
				pi.setRecord_ID(get_ID());
	
				List<ProcessInfoParameter> po = new ArrayList<ProcessInfoParameter>();
				po.add(new ProcessInfoParameter("CloseTax","Y",null,"",""));
				//
				ProcessInfoParameter[] pp = new ProcessInfoParameter[po.size()];
				po.toArray(pp);
				pi.setParameter(pp);
				//	Execute Process
				ProcessParameterPanel pu = new ProcessParameterPanel(0, pi);
				ProcessCtl.process(this, 0, (IProcessParameter) pu, pi, null);
			}
			
			//	Re-Check
			if (!m_justPrepared)
			{
				String status = prepareIt();
				if (!DocAction.STATUS_InProgress.equals(status))
					return status;
			}
	
			m_processMsg = ModelValidationEngine.get().fireDocValidate(this, ModelValidator.TIMING_BEFORE_COMPLETE);
			if (m_processMsg != null)
				return DocAction.STATUS_Invalid;
			//	Implicit Approval
			if (!isApproved())
				approveIt();
			log.info(toString());
			
			StringBuffer info = new StringBuffer();
			
			m_processMsg = info.toString().trim();
			setProcessed(true);
			setDocAction(DOCACTION_Close);
			return DocAction.STATUS_Completed;
		}	//	completeIt

		/* Save array of documents to process AFTER completing this one */
		ArrayList<PO> docsPostProcess = new ArrayList<PO>();

		public ArrayList<PO> getDocsPostProcess() {
			return docsPostProcess;
		}

		/**
		 * 	Void Document.
		 * 	@return true if success
		 */
		public boolean voidIt()
		{
			boolean result = true;
			if (!haveCorrectDocument()) {
				if ( DocAction.STATUS_Completed.equals(getDocStatus()) && isPosted()) {
					result = reverseCorrectIt();
				}
				if (result) {
					setProcessed(true);
					setDocAction(DOCACTION_None);
				}
			} else {
				result = false;
			}
			return result;	
		}	//	voidIt

		/**
		 * 	Close Document.
		 * 	@return true if success
		 */
		public boolean closeIt()
		{
			log.info(toString());
			// Before Close
			m_processMsg = ModelValidationEngine.get().fireDocValidate(this,ModelValidator.TIMING_BEFORE_CLOSE);
			if (m_processMsg != null)
				return false;

			setProcessed(true);
			setDocAction(DOCACTION_None);

			// After Close
			m_processMsg = ModelValidationEngine.get().fireDocValidate(this,ModelValidator.TIMING_AFTER_CLOSE);
			if (m_processMsg != null)
				return false;
			return true;
		}	//	closeIt

		/**
		 * 	Reverse Correction - same date
		 * 	@return true if success
		 */
		public boolean reverseCorrectIt()
		{
			boolean result = false;
			log.info(toString());
			if (!haveCorrectDocument()) {
				result = createCorrectDocument();
			}
			return result;
		}	//	reverseCorrectIt

		/**
		 * @return
		 */
		private boolean createCorrectDocument() {
			Mhrmpayment reversal = copyFrom (this, getDateFrom(), getDateAcct(),getC_DocType_ID(), get_TrxName());
			if (reversal == null) {
				m_processMsg = "Could not create Invoice Reversal";
				return false;
			}
			reversal.setReversal(true);
			reversal.setName("!!!!-" + reversal.getName());
			reversal.addDescription("{->" + getDocumentNo() + ")");
			reversal.sethrm_amountsalary(gethrm_amountsalary().negate());
			reversal.sethrm_CNalog(gethrm_CNalog().negate());
			reversal.sethrm_COtch(gethrm_COtch().negate());
			reversal.sethrm_IPN(gethrm_IPN().negate());
			reversal.sethrm_OPV(gethrm_OPV().negate());

			Mhrmpaymentlist[] iLines = reversal.getLines(false);
			for (int i = 0; i < iLines.length; i++)
			{
				Mhrmpaymentlist iLine = iLines[i];
				iLine.sethrm_amountsalary(iLine.gethrm_amountsalary().negate());
				iLine.sethrm_OPV(iLine.gethrm_OPV().negate());
				iLine.sethrm_COtch(iLine.gethrm_COtch().negate());
				iLine.sethrm_IPN(iLine.gethrm_IPN().negate());
				iLine.sethrm_CNalog(iLine.gethrm_CNalog().negate());
				iLine.sethrm_total(iLine.gethrm_total().negate());
				iLine.saveEx(get_TrxName());
	        }
			setProcessed(true);
			
			reversal.setReversal_ID(gethrm_payment_ID()); // TODO
			reversal.saveEx(get_TrxName());
			if (!reversal.processIt(DocAction.ACTION_Complete))	{
				m_processMsg = "Reversal ERROR: " + reversal.getProcessMsg();
				return false;
			}
			reversal.closeIt();
			reversal.setProcessing (false);
			reversal.setDocStatus(DOCSTATUS_Drafted);
			reversal.setDocAction(DOCACTION_Complete);
			reversal.saveEx(get_TrxName());
			m_processMsg = reversal.getDocumentNo();
			setReversal_ID(reversal.gethrm_payment_ID());
			addDescription("(" + reversal.getDocumentNo() + "<-)");
			return true;
		}

		/**
		 * @return
		 */
		public boolean haveCorrectDocument() {
			boolean result = getReversal_ID() > 0;
			return result;
		}

		/**
		 * 	Reverse Accrual - none
		 * 	@return false
		 */
		public boolean reverseAccrualIt()
		{
			log.info(toString());
			// Before reverseAccrual
			m_processMsg = ModelValidationEngine.get().fireDocValidate(this,ModelValidator.TIMING_BEFORE_REVERSEACCRUAL);
			if (m_processMsg != null)
				return false;

			// After reverseAccrual
			m_processMsg = ModelValidationEngine.get().fireDocValidate(this,ModelValidator.TIMING_AFTER_REVERSEACCRUAL);
			if (m_processMsg != null)
				return false;

			return false;
		}	//	reverseAccrualIt

		/**
		 * 	Re-activate
		 * 	@return false
		 */
		public boolean reActivateIt()
		{
			log.info(toString());
			// Before reActivate
			m_processMsg = ModelValidationEngine.get().fireDocValidate(this,ModelValidator.TIMING_BEFORE_REACTIVATE);
			if (m_processMsg != null)
				return false;
			setPosted(false);
			setProcessed(false);
			setDocAction(DOCACTION_Complete);
			// After reActivate
			m_processMsg = ModelValidationEngine.get().fireDocValidate(this,ModelValidator.TIMING_AFTER_REACTIVATE);
			if (m_processMsg != null)
				return false;


			return true;
		}	//	reActivateIt

		/**
		 * 	Get Process Message
		 *	@return clear text error message
		 */
		public String getProcessMsg()
		{
			return m_processMsg;
		}	//	getProcessMsg


		/**
		 * 	Document Status is Complete or Closed
		 *	@return true if CO, CL or RE
		 */
		public boolean isComplete()
		{
			String ds = getDocStatus();
			return DOCSTATUS_Completed.equals(ds)
				|| DOCSTATUS_Closed.equals(ds)
				|| DOCSTATUS_Reversed.equals(ds);
		}	//	isComplete

		@Override
		public String getSummary() {
			return null;
		}

		@Override
		public String getDocumentNo() {
			return null;
		}

		@Override
		public String getDocumentInfo() {
			return null;
		}

		@Override
		public File createPDF() {
			return null;
		}

		@Override
		public int getDoc_User_ID() {
			return 0;
		}

		@Override
		public int getC_Currency_ID() {
			return 0;
		}

		@Override
		public BigDecimal getApprovalAmt() {
			return null;
		}
		
		protected void getAD_Process_ID() {
			
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			
			// 
			String sql_ = "select distinct t.ad_process_id from ad_process t \n "+ 
						  "where (lower(t.value) like lower('GenerateAmountSalary%') or lower(t.name) like lower('GenerateAmountSalary%')) "+ 
					      "and t.isactive='Y'";
			try
			{
				pstmt = DB.prepareStatement(sql_, null);
				rs = pstmt.executeQuery();
				if (rs.next())
					AD_Process_ID = rs.getInt(1);
			}
			catch (SQLException e)
			{
				log.log(Level.SEVERE, "product", e);
			}
			finally
			{
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}
		}
		
		private Waiting m_waiting;
		
		@Override
		public void lockUI(ProcessInfo pi) {
			
			JFrame frame = Env.getFrame((Container)m_parent);
			m_waiting = new Waiting (frame, Msg.getMsg(Env.getCtx(), "Processing"), false, pi.getEstSeconds());	
		}

		@Override
		public void unlockUI(ProcessInfo pi) {

			if (m_waiting != null)
				m_waiting.dispose();
			m_waiting = null;
		}

		@Override
		public boolean isUILocked() {
			return false;
		}

		@Override
		public void executeASync(ProcessInfo pi) {
			
		}
		
		public static BigDecimal getSalarySum_Result(int C_BPartner_ID, Timestamp dt) {
			//
		    PreparedStatement pstmt = null;
			ResultSet rs = null;
			BigDecimal result = new BigDecimal(0);
				
			// 
			String sql_ = "SELECT TOTALSUM FROM HRM_SALARYSUM_V \n" +
							" WHERE AD_CLIENT_ID="  + Env.getAD_Client_ID(Env.getCtx()) + " \n" +
								" AND C_BPARTNER_ID=" + C_BPartner_ID + " \n" +
								" AND DATEFROM BETWEEN DATE_TRUNC('MONTH', " + DB.TO_DATE(dt, true) + ") " +
								" AND DATE_TRUNC('MONTH'," + DB.TO_DATE(dt, true) + ") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL";
				try
				{
					pstmt = DB.prepareStatement(sql_,null);
					rs = pstmt.executeQuery();	
					if(rs.next())
						result = rs.getBigDecimal(1);
				}
				catch (SQLException e)
				{
					s_log.log(Level.SEVERE, "product", e);
				}
				finally
				{
					DB.close(rs, pstmt);
					rs = null; pstmt = null;
				}	
				
			return result;
		}
		
		public static BigDecimal getSalarySum_OPV(int C_BPartner_ID, Timestamp dt) {
			//
		    PreparedStatement pstmt = null;
			ResultSet rs = null;
			BigDecimal result = new BigDecimal(0);
				
			// 
			String sql_ = "SELECT OPV FROM HRM_SALARYSUM_V \n" +
							" WHERE AD_CLIENT_ID="  + Env.getAD_Client_ID(Env.getCtx()) + " \n" +
								" AND C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
								" AND OPV !=0 \n " +
								" AND DATEFROM BETWEEN DATE_TRUNC('MONTH', " + DB.TO_DATE(dt, true) + ") " +
								" AND DATE_TRUNC('MONTH'," + DB.TO_DATE(dt, true) + ") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL";
				try
				{
					pstmt = DB.prepareStatement(sql_,null);
					rs = pstmt.executeQuery();	
					if(rs.next())
						result = rs.getBigDecimal(1);
				}
				catch (SQLException e)
				{
					s_log.log(Level.SEVERE, "product", e);
				}
				finally
				{
					DB.close(rs, pstmt);
					rs = null; pstmt = null;
				}	
				
			return result;
		}		
		
		public static BigDecimal getSalarySum_IPN(int C_BPartner_ID, Timestamp dt) {
			//
		    PreparedStatement pstmt = null;
			ResultSet rs = null;
			BigDecimal result = new BigDecimal(0);
				
			// 
			String sql_ = "SELECT IPN FROM HRM_SALARYSUM_V \n" +
							" WHERE AD_CLIENT_ID="  + Env.getAD_Client_ID(Env.getCtx()) + " \n" +
								" AND C_BPARTNER_ID=" + C_BPartner_ID + " \n" +
								" AND DATEFROM BETWEEN DATE_TRUNC('MONTH', " + DB.TO_DATE(dt, true) + ") " +
								" AND DATE_TRUNC('MONTH'," + DB.TO_DATE(dt, true) + ") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL";
				try
				{
					pstmt = DB.prepareStatement(sql_,null);
					rs = pstmt.executeQuery();	
					if(rs.next())
						result = rs.getBigDecimal(1);
				}
				catch (SQLException e)
				{
					s_log.log(Level.SEVERE, "product", e);
				}
				finally
				{
					DB.close(rs, pstmt);
					rs = null; pstmt = null;
				}	
				
			return result;
		}
		
		/**	Statement Lines	*/
		private Mhrmpaymentlist[] pay_lines;
		
		/**
		 * 	Get HR Payment Lines
		 * 	@param whereClause starting with AND
		 * 	@return lines
		 */
		private Mhrmpaymentlist[] getLines (String whereClause)
		{
			String whereClauseFinal = "hrm_payment_ID=? ";
			if (whereClause != null)
				whereClauseFinal += whereClause;
			List<Mhrmpaymentlist> list = new Query(getCtx(), I_hrm_payment_list.Table_Name, whereClauseFinal, get_TrxName())
											.setParameters(gethrm_payment_ID())
											.list();
			return list.toArray(new Mhrmpaymentlist[list.size()]);
		}	//	getLines

		/**
		 * 	Get Payment Lines
		 * 	@param requery
		 * 	@return lines
		 */
		public Mhrmpaymentlist[] getLines (boolean requery)
		{
			if (pay_lines == null || pay_lines.length == 0 || requery)
				pay_lines = getLines(null);
			set_TrxName(pay_lines, get_TrxName());
			return pay_lines;
		}	//	getLines

		/**
		 * 	Get Payment line
		 * 	@return lines
		 */
		public Mhrmpaymentlist[] getLines()
		{
			return getLines(false);
		}	//	getLines
		
		/**
		 * Get Payment Lines assorted
		 * @param paymentID
		 * @return
		 */
		public  Mhrmpaymentlist[] getLinesPaymentSort (int paymentID)
		{
			
			ArrayList<Mhrmpaymentlist> result = new ArrayList<Mhrmpaymentlist>();
			Mhrmpaymentlist line = null;
			String sql = "select * from hrm_payment_list t \n"+
						"join c_bpartner r on r.c_bpartner_id=t.c_bpartner_id \n"+
						"where t.hrm_payment_id="+paymentID+" \n"+
						"order by r.lastname";
			
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			try
			{
				pstmt = DB.prepareStatement(sql, null);
				rs = pstmt.executeQuery();
				while(rs.next()){
					line = new Mhrmpaymentlist(getCtx(), rs, get_TrxName());
					result.add(line);
				}
			}
			catch (SQLException e)
			{
				log.log(Level.SEVERE, "SQL=" + sql, e);
			}
			finally {
				DB.close(rs, pstmt);
				rs = null; pstmt = null;
			}
			return result.toArray(new  Mhrmpaymentlist[result.size()]);
		}
		
		/**
		 * used in Mhrmstatement.java 
		 * int C_BPartner_ID
		 * int hrm_payment_list_ID
		 * String column. Sample: OPV, COTCH...
		 *  */
		public static BigDecimal getSalSumColumn(int C_BPartner_ID, int hrm_payment_list_ID, String column) {
			//
		    PreparedStatement pstmt = null;
			ResultSet rs = null;
			BigDecimal result = new BigDecimal(0);
			
			if (C_BPartner_ID == 0)
				return result;
			// 
			String sql_ = "SELECT "+column+" FROM HRM_SALARYSUM_V \n" +
							" WHERE C_BPARTNER_ID=" + C_BPartner_ID + " \n" +
							" AND HRM_PAYMENT_LIST_ID=" + hrm_payment_list_ID;
				try
				{
					pstmt = DB.prepareStatement(sql_,null);
					rs = pstmt.executeQuery();	
					if(rs.next())
						result = rs.getBigDecimal(1);
				}
				catch (SQLException e)
				{
					s_log.log(Level.SEVERE, "product", e);
				}
				finally
				{
					DB.close(rs, pstmt);
					rs = null; pstmt = null;
				}	
				
			return result;
		}
		
		public static int getSalarySumCount(Timestamp dt) {
			//
		    PreparedStatement pstmt = null;
			ResultSet rs = null;
			int result = 0;
							
			// 
			String sql_ = "SELECT COUNT(C_BPARTNER_ID) FROM HRM_SALARYSUM_V \n" +
							" WHERE AD_CLIENT_ID="  + Env.getAD_Client_ID(Env.getCtx()) + " \n" +
								" AND DATEFROM BETWEEN DATE_TRUNC('MONTH', " + DB.TO_DATE(dt, true) + ") " +
								" AND DATE_TRUNC('MONTH'," + DB.TO_DATE(dt, true) + ") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL";
				try
				{
					pstmt = DB.prepareStatement(sql_,null);
					rs = pstmt.executeQuery();	
					if(rs.next())
					result = rs.getInt(1);											
				}
				catch (SQLException e)
				{
					s_log.log(Level.SEVERE, "product", e);
				}
				finally
				{
					DB.close(rs, pstmt);
					rs = null; pstmt = null;
				}	
				
			return result;
	}
		
		public static BigDecimal getSalarySum_COTCH(int C_BPartner_ID, Timestamp dt) {
			//
		    PreparedStatement pstmt = null;
			ResultSet rs = null;
			BigDecimal result = new BigDecimal(0);
				
			// 
			String sql_ = "SELECT COTCH FROM HRM_SALARYSUM_V \n" +
							" WHERE AD_CLIENT_ID="  + Env.getAD_Client_ID(Env.getCtx()) + " \n" +
								" AND C_BPARTNER_ID=" + C_BPartner_ID + " \n " +
								" AND COTCH != 0 \n " +
								" AND DATEFROM BETWEEN DATE_TRUNC('MONTH', " + DB.TO_DATE(dt, true) + ") " +
								" AND DATE_TRUNC('MONTH'," + DB.TO_DATE(dt, true) + ") + '1 MON'::INTERVAL - '1 DAY'::INTERVAL";
				try
				{
					pstmt = DB.prepareStatement(sql_,null);
					rs = pstmt.executeQuery();	
					if(rs.next())
						result = rs.getBigDecimal(1);
				}
				catch (SQLException e)
				{
					s_log.log(Level.SEVERE, "product", e);
				}
				finally
				{
					DB.close(rs, pstmt);
					rs = null; pstmt = null;
				}	
				
			return result;
		}	
		
		public static Mhrmpayment copyFrom (Mhrmpayment from, Timestamp dateDoc, Timestamp dateAcct, int C_DocTypeTarget_ID, String trxName) {
			Mhrmpayment to = new Mhrmpayment(from.getCtx(),0,trxName);
			copyValues(from, to);
			
			to.set_ValueNoCheck ("hrm_payment_ID", I_ZERO);
			to.set_ValueNoCheck ("DocumentNo", null);
			//
			to.setDocStatus (DOCSTATUS_Drafted);		//	Draft
			to.setDocAction(DOCACTION_Complete);
			//
			to.setDateFrom (dateDoc);
			to.setDateAcct (dateAcct);
			//
			to.setIsApproved (false);
			//
			to.setIsTransferred (false);
			to.setPosted (false);
			to.setProcessed (false);
			//[ 1633721 ] Reverse Documents- Processing=Y
			to.setProcessing(false);
			//	delete references
			to.saveEx(trxName);

			//	Lines
			// Check lines exist before copy
			if ( from.getLines(false).length > 0 )
			{
				if (to.copyListLinesFrom(from) == 0)
					throw new IllegalStateException("Could not create List");
			}
			
			return to;
		}

		public Mhrmpaymentline[] getPaymentLines() {
			return getPaymentLines(null);
		}
		
		/**
		 * @return
		 */
		public Mhrmpaymentline[] getPaymentLines(String whereClause) {
			String whereClauseFinal = "hrm_payment_ID=? ";
			if (whereClause != null)
				whereClauseFinal += whereClause;
			List<Mhrmpaymentline> list = new Query(getCtx(), I_hrm_paymentline.Table_Name, whereClauseFinal, get_TrxName())
											.setParameters(gethrm_payment_ID())
											.list();
			return list.toArray(new Mhrmpaymentline[list.size()]);
		}

		public int copyListLinesFrom (Mhrmpayment other) {
			if (isProcessed() || isPosted() || other == null)
				return 0;
			Mhrmpaymentlist[] fromLines = other.getLines(false);
			int count = 0;
			for (int i = 0; i < fromLines.length; i++)
			{
				Mhrmpaymentlist line = new Mhrmpaymentlist (getCtx(), 0, get_TrxName());
				Mhrmpaymentlist fromLine = fromLines[i];
				PO.copyValues (fromLine, line, fromLine.getAD_Client_ID(), fromLine.getAD_Org_ID());
				line.sethrm_payment_ID(gethrm_payment_ID());
				line.set_ValueNoCheck ("hrm_payment_list_ID", I_ZERO);	// new
				if (line.save(get_TrxName()))
					count++;
			}
			if (fromLines.length != count)
				log.log(Level.SEVERE, "Line difference - From=" + fromLines.length + " <> Saved=" + count);
			return count;
		}

		public int copyPaymentLinesFrom (Mhrmpayment other) {
			if (isProcessed() || isPosted() || other == null)
				return 0;
			Mhrmpaymentline[] fromLines = other.getPaymentLines();
			int count = 0;
			for (int i = 0; i < fromLines.length; i++)
			{
				Mhrmpaymentline line = new Mhrmpaymentline (getCtx(), 0, get_TrxName());
				Mhrmpaymentline fromLine = fromLines[i];
				PO.copyValues (fromLine, line, fromLine.getAD_Client_ID(), fromLine.getAD_Org_ID());
				line.sethrm_payment_ID(gethrm_payment_ID());
				line.set_ValueNoCheck ("hrm_paymentline_ID", I_ZERO);	// new
				if (line.save(get_TrxName()))
					count++;
			}
			if (fromLines.length != count)
				log.log(Level.SEVERE, "Line difference - From=" + fromLines.length + " <> Saved=" + count);
			return count;
		}

		public void addDescription (String description)
		{
			String desc = getDescription();
			if (desc == null)
				setDescription(description);
			else
				setDescription(desc + " | " + description); 
		}	//	addDescription 

		public boolean haveBPartner(int C_BPartner_ID) {
			boolean result = false;
			for(Mhrmpaymentlist pl: getLines(false)) {
				result = (pl.getC_BPartner_ID() == C_BPartner_ID) || result;
				if (result) break;
			}
			return result;
		}
}
